home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Shareware Grab Bag
/
Shareware Grab Bag.iso
/
019
/
socsec.arc
/
SOCSEC.DOC
next >
Wrap
Text File
|
1986-11-23
|
9KB
|
178 lines
SOCIAL SECURITY RETIREMENT BENEFIT CALCULATION SPREADSHEET
By EARL PATIN, 9231 ROANOKE DRIVE, EL PASO, TEXAS 79924
GENERAL INFORMATION:
1. This spreadsheet, designed to be run with LOTUS 123, Release 1A,
may be run with that release or higher releases of LOTUS 123 and LOTUS
SYMPHONY.
2. The formulae used in the calculations are correct as of November
1986. Due to the political nature of the Social Security Program, we
cannot assure that this will be true at any time in the future.
Undoubtedly, they will change as Congress makes revisions.
3. We do not guarantee that the final figures will agree exactly
with those which might be produced by the Social Security Administration.
However, they will be within the "ball-park" and could be used in
planning for the future. As always, it would be wise to look at these
figures with a "grain of salt"--Congress may change the whole system
in the blink of an eye.
4. Here is a brief summary of the data used in developing this
spreadsheet:
a. Earnings credited and used in the spreadsheet do not extend
before 1951. This was done to be in line with current
procedures of the Social Security Administration, and due to
the fact the the number of creditable years used for the
calculations do not require data before that year.
b. The Primary Insurance Amount (PIA) is a three-tiered percentage
of the Average Indexed Monthly Earnings (AIME). The AIME is
calculated on a variable number of years of earnings (depending
on when you were born). For the purposes of this spreadsheet,
the calculations are based on the high 35 years of earnings,
which applies to all persons born after 1928. If the person
for whom the calculations are being made was born before or in
1928, a different number of years must be used. These are:
1916-22 1917-23 1918-24 1919-25 1920-26 1921-27 1922-28
1923-29 1924-30 1925-31 1926-32 1927-33 1928-34
Should this change be necessary, the cells used in calculating
the AIME must be modified accordingly. The AIME cell has been
labeled in the workarea space of the spreadsheet and the
formula used has been written to show the number of years
used in the calculation: "$O$1/(12*35)". Thus, it is only
necessary to change the '35' in the formula to the appropriate
number of years. Also, it necessary to change the number of
years of indexed earnings. The INDEXED EARNINGS cell is a
function formula (@SUM($F$16..$F$50)) and the last cell
referenced in the formula must be changed to include the
same number of cells as was changed in the AIME cell. An
example is: (for a person born in 1920) the AIME cell
would be changed to "$O$1/(12*26) and the INDEXED EARNINGS
cell would be changed to "@SUM($f$16..$f$41)". Both cells
may be quickly accessed by doing a "GOTO M1".
c. Since the earning power of the dollar has gradually eroded over
the years, an index factor is used to determine the present
purchasing power of previous earnings credited for the benefits.
This index factor extends through the year of 1983. Thereafter,
the current value of the creditable earnings are used. These
index factors have been included in the spreadsheet and are
used in the calculations to determine the benefit. Don't
worry about them--they are automatic.
d. Although due care was used in accumulating the various index
factors, percentages, and rules for calculating, we are not
part of the Social Security Administration, and cannot guaran-
tee the absolute validity of such figures. This spreadsheet
does, however, give an estimate of the benefits which should
be in the range of those calculated by the Administration.
Further, it does provide you with a way of testing and making
several runs, based on various years of retirement, and may
help you in planning on just what additional sources you would
have to acquire to assure a financially secure retirement.
EXAMPLE SPREADSHEET (SOCSECE.WKS)
Included, in addition to the running spreadsheet, is a spreadsheet
entitled 'SOCSECE.WKS'. It is a fictional spreadsheet based on the
following assumptions:
The person for whom this is run intends to retire in the year of
2000. He was born in the 1930's and first paid social security in
1956. His estimated month of retirement is July 2000, thus, the last
year of estimated earnings has been calculated to June 2000. His
earnings have been above the maximum creditable for Social Security
since he started paying the Social Security Tax and the earnings past
1986 have been estimated to be same as 1986 in order to preserve the
value of the retirement benefit in the current value of the dollar.
GATHERING INFORMATION FOR USE IN PERSONAL CALCULATIONS
Naturally, you should use the actual earnings credited for Social
Security in running the spreadsheet for any particular individual.
This information may be from actual records or may be obtained from the
Social Security Administration. To obtain the figures from Social
Security Administration records, obtain, from the local Social Security
Office, a copy of Form SSA-7004 and submit it to appropriate location.
In time, you will receive the earnings credited. Even though it takes
quite a while to receive this information, do not despair--it will
eventually arrive.
RUNNING THE SPREADSHEET
A. Enter the yearly earnings credited for Social Security in
the column labeled 'EARNINGS' for the year shown in the
column labeled 'YEAR'. Be careful and double-check these
figures. They should not exceed the amount shown in the
column labeled 'MAX' for any year.
B. For all years subsequent to the last available figure until
the year in which you wish to retire, use the last available
figure (1986). Even though the actual figures may change in
the future years, this figure will give the benefit in the
current value of the dollar.
C. Enter annual earnings only up to the year in which you intend
to retire. In that last year, it would be wise to enter only
the amount for the number of months prior to retirement. To
do this, divide the estimated yearly earnings by 12 and
multiply this amount by the number of months prior to the
month in which you wish to retire. This will make the final
figure closer to actuality.
D. After all the earnings have been entered, tell 123 to 'GO'
by holding down the <ALT> key and hitting the 'G' key. This
will enable a group of macros which will perform the yearly
index calculations, abstract and total the high 35 years of
earnings, calculate the AIME, calculate the PIA, calculate
the amount of individual taxes paid for each year, and,
finally, calculate and enter the applicable totals in the
appropriate sections of the spreadsheet ahead of the yearly
figures.
REMEMBER: <ALT>G to run
E. If you wish to have a hardcopy printout of the main area
of the spreadsheet, hold down the <ALT> key and hit 'P' to
cause automatic printing of the calculated data.
REMEMBER: <ALT>P to print
F. If you wish to electronically save the spreadsheet, hold
down the <ALT> key and hit 'S'. This will save the spread-
sheet under the name of "SOCSEC.WKS".
REMEMBER: <ALT>S to save
G. To go on to retrieve other worksheets from your 123 database,
hold down the <ALT> key and hit 'R'. This will cause 123
to bring up the available spreadsheets in your data section
for selection and use.
REMEMBER: <ALT>R to retrieve
H. Finally, to leave 123 after using this spreadsheet, hold
down the <ALT> key and hit 'Q'. This will cause 123 to
return to DOS.
REMEMBER: <ALT>Q to quit
ADDITIONAL FACTS ABOUT THE SPREADSHEET
The main area of the spreadsheet (Columns A through G) contain
information of interest to the recipient of the information. In
addition, there are work areas, although necessary for running the
calculations, are usually not of interest to the person who is only
interested in his personal data. These figures include the cells
for calculating the high 35 years of earnings, the AIME, the PIA,
and the macros necessary to run. Should you be interested in these
(especially if it is necessary to change the number of years used
in the calculations), you will find them starting in cell M1. A
GOTO cell M1 will display all of the work areas and macros used
in the spreadsheet.